性能设计篇之"数据库扩展" [2026重制版]

@Configuration
public class DataSourceConfig {

    /**
     * 动态数据源 — 根据注解自动选择主/从
     */
    @Bean
    @Primary
    public DynamicDataSource dynamicDataSource(
            @Qualifier("masterDataSource") DataSource masterDataSource,
            @Qualifier("slaveDataSource") DataSource slaveDataSource) {

        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource);
        targetDataSources.put("slave", slaveDataSource);

        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSources);
        dataSource.setDefaultTargetDataSource(masterDataSource); // 默认走主库

        return dataSource;
    }
}

/**
 * 自定义注解 — 标记读操作走从库
 */
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnly {
}

/**
 * AOP 切面 — 根据 @ReadOnly 注解切换数据源
 */
@Aspect
@Component
@Slf4j
public class DataSourceAspect {

    @Around("@annotation(readOnly)")
    public Object around(ProceedingJoinPoint joinPoint, ReadOnly readOnly) throws Throwable {
        try {
            // 切换到从库（读）
            DynamicDataSourceContextHolder.setSlave();
            log.debug("使用从库执行: {}", joinPoint.getSignature().getName());
            return joinPoint.proceed();
        } finally {
            DynamicDataSourceContextHolder.clear();
        }
    }
}

/**
 * 使用示例
 */
@Service
public class ProductService {

    /** 写操作 — 自动走主库 */
    public void createProduct(Product product) {
        productMapper.insert(product);
    }

    /** 读操作 — 注解标记走从库 */
    @ReadOnly
    public Product getProductById(String id) {
        return productMapper.selectById(id);
    }

    /** 需要强一致性的读操作 — 不加注解，走主库 */
    public Product getProductForUpdate(String id) {
        return productMapper.selectByIdForUpdate(id);
    }
}

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>5.5.0</version>
</dependency>
<!-- 如果使用 Spring Boot Starter -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-spring-boot-starter</artifactId>
    <version>5.5.0</version>
</dependency>

# application-sharding.yml
spring:
  shardingsphere:
    # ====== 数据源配置 ======
    datasource:
      names: ds0,ds1,ds0-read-0,ds0-read-1,ds1-read-0,ds1-read-1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://mysql-master-0:3306/order_db_0?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
        username: ${DB_USERNAME}
        password: ${DB_PASSWORD}
      ds0-read-0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://mysql-replica-0a:3306/order_db_0?useSSL=false&serverTimezone=Asia/Shanghai
        username: ${DB_USERNAME}
        password: ${DB_PASSWORD}
      ds0-read-1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://mysql-replica-0b:3306/order_db_0?useSSL=false&serverTimezone=Asia/Shanghai
        username: ${DB_USERNAME}
        password: ${DB_PASSWORD}
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://mysql-master-1:3306/order_db_1?useSSL=false&serverTimezone=Asia/Shanghai
        username: ${DB_USERNAME}
        password: ${DB_PASSWORD}
      ds1-read-0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://mysql-replica-1a:3306/order_db_1?useSSL=false&serverTimezone=Asia/Shanghai
        username: ${DB_USERNAME}
        password: ${DB_PASSWORD}
      ds1-read-1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://mysql-replica-1b:3306/order_db_1?useSSL=false&serverTimezone=Asia/Shanghai
        username: ${DB_USERNAME}
        password: ${DB_PASSWORD}

    # ====== 分片规则 ======
    rules:
      sharding:
        # 表分片规则
        tables:
          t_order:
            actual-data-nodes: ds${0..1}.t_order_${0..3}
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-table-inline
          t_order_item:
            actual-data-nodes: ds${0..1}.t_order_item_${0..3}
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-item-inline

        # 绑定表规则（避免跨库关联查询）
        binding-tables:
          - t_order, t_order_item

        # 广播表（小表全量复制到每个节点）
        broadcast-tables:
          - t_dict_config
          - t_system_config

        # 默认分片算法
        default-database-strategy:
          standard:
            sharding-column: user_id
            sharding-algorithm-name: database-inline

        # 自定义分片算法
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds${user_id % 2}
          order-table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_${order_id % 4}
          order-item-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_item_${order_id % 4}

      # ====== 读写分离规则 ======
    readwrite-splitting:
      data-sources:
        ds0:
          write-data-source-name: ds0
          read-data-source-names: ds0-read-0, ds0-read-1
          load-balancer-name: round_robin
        ds1:
          write-data-source-name: ds1
          read-data-source-names: ds1-read-0, ds1-read-1
          load-balancer-name: round_robin
      load-balancers:
        round_robin:
          type: ROUND_ROBIN

    # ====== 属性配置 ======
    props:
      sql-show: true                    # 显示 SQL 解析结果
      sql-simple: false                  # 简化 SQL 显示

# 使用 TiUP 安装 TiDB v7.1
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirror.pingcap.com/install.sh | sh
source ~/.bashrc
tiup cluster

# 部署一个测试集群
tiup cluster deploy my-tidb v7.1 ./topology.yaml --user root
tiup cluster start my-tidb

# topology.yaml — TiDB 集群拓扑
global:
  user: "tidb"
  ssh_port: 22
  deploy_dir: "/data/tidb-deploy"
  data_dir: "/data/tidb-data"

server_configs:
  pd:
    replication.max-replicas: 3
    schedule.leader-schedule-limit: 4
    schedule.region-schedule-limit: 2048
    replication.location-labels: ["zone"]
  tikv:
    storage.scheduler-worker-pool-size: 4
    storage.block-cache.capacity: "4GB"
    raftstore.apply-pool-size: 3
    raftstore.store-pool-size: 3
    rocksdb.defaultcf.compression: "lz4"
    rocksdb.writecf.compression: "lz4"
  tidb:
    log.level: info
    performance.max-procedures: 500
    performance.stmt-count-limit: 5000
    prepared-plan-cache.enabled: true
    prepared-plan-cache.capacity: 5000
  tiflash:
    logger.level: "info"

pd_servers:
  - host: 10.0.1.1

tidb_servers:
  - host: 10.0.1.2

tikv_servers:
  - host: 10.0.1.3
  - host: 10.0.1.4
  - host: 10.0.1.5

tiflash_servers:
  - host: 10.0.1.6

monitoring_servers:
  - host: 10.0.1.7

grafana_servers:
  - host: 10.0.1.7

/**
 * 订单实体 — 必须包含分片键
 */
@Data
@Table(name = "t_order")
public class Order {
    @TableId(type = IdType.ASSIGN_ID)
    private Long orderId;           // 业务主键（也是分片键）

    private Long userId;             // 用户 ID（数据库分片键）

    private BigDecimal totalAmount;
    private Integer status;

    /** 重要：关联表必须使用相同的分片键和分片算法！ */
    private List<OrderItem> items;
}

/**
 * 分片键设计原则：
 *
 * 1. 选择高基数列作为分片键（如 userId, orderId）
 * 2. 分片键必须在所有查询条件中出现
 * 3. 避免 Cross-Shard JOIN（跨分片关联查询）
 * 4. 全局唯一 ID 生成使用雪花算法或号段模式
 */

/**
 * 雪花算法 ID 生成器
 */
@Component
public class SnowflakeIdGenerator {

    private final Snowflake snowflake = new Snowflake(
        1,   // workerId
        1    // datacenterId
    );

    public long nextId() {
        return snowflake.nextId();
    }
}

┌─────────────────────────────────────────────────────┐
│           数据库扩展推荐路径 (2026)                     │
├─────────────────────────────────────────────────────┤
│                                                      │
│  Phase 1: 读写分离                                    │
│  ├── MySQL 主从复制 (1主N从)                          │
│  └── 中间件: ShardingSphere / ProxySQL               │
│                                                      │
│  Phase 2: 分库分表                                    │
│  ├── ShardingSphere-JDBC (Java 嵌入式)               │
│  ├── Vitess (语言无关 Proxy)                         │
│  └── 分片键: user_id / tenant_id                     │
│                                                      │
│  Phase 3: NewSQL 迁移                                 │
│  ├── TiDB (HTAP 场景首选)                            │
│  ├── CockroachDB (多地域场景)                        │
│  └── 渐进式迁移: 双写 → 切流                         │
│                                                      │
│  Phase 4: CQRS + Event Sourcing                      │
│  ├── 写模型: 事件驱动                                │
│  ├── 读模型: 物化视图/CQRS                           │
│  └── 最终一致性保证                                   │
│                                                      │
└─────────────────────────────────────────────────────┘